<?php
namespace Home\Controller;
use Think\Controller;
class ExcelController extends ExtendController{

	public function excel(){
        $this->_cmd('_excel_');
    }
    
    //上传excel文件
    protected  function _excel_uploadExcel(){
    	$excelDir='./Uploads';
        if (!file_exists($excelDir)) {
    		mkdir($excelDir);
    	}
    	$upload = new \Think\Upload();// 实例化上传类
    	$upload->maxSize   =     3145728 ;// 设置附件上传大小
    	$upload->exts      =     array('xls', 'xlsx');// 设置附件上传类
        $upload->savePath  =      '/'; // 设置附件上传目录
        // 上传文件
        $info   =   $upload->uploadOne($_FILES['excelUpload']);//得到上传文件的基本信息
        #存放的文件名称要唯一
        
        $filename = "./Uploads".$info['savepath'].$info['savename'];
        //echo $filename;exit;
        $exts = $info['ext'];//文件类型
        if(!$info) {// 上传错误提示错误信息
              $this->error($upload->getError());
          }else{// 上传成功
                 $this->_excel_goods_import($filename, $exts);
        }
    }
   
    //导入数据方法
    protected function _excel_goods_import($filename, $exts='xls')
    {  
    	//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
    	import("Org.Util.PHPExcel");
    	//创建PHPExcel对象，注意，不能少了\
    	$PHPExcel=new \PHPExcel();
    	//如果excel文件后缀名为.xls，导入这个类
    	if($exts == 'xls'){
    		import("Org.Util.PHPExcel.Reader.Excel5");
    		$PHPReader=new \PHPExcel_Reader_Excel5();
    	}else if($exts == 'xlsx'){
    		import("Org.Util.PHPExcel.Reader.Excel2007");
    		$PHPReader=new \PHPExcel_Reader_Excel2007();
    	}
    	//载入文件
    	$PHPExcel=$PHPReader->load($filename);
    	//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
    	$currentSheet=$PHPExcel->getSheet(0);
    	//获取总列数
    	$allColumn=$currentSheet->getHighestColumn();
    	//获取总行数
    	$allRow=$currentSheet->getHighestRow();
    	//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    	
    	#分析EXCEL文件只需要分析第一行
    	
    	for($currentRow=1;$currentRow<=$allRow;$currentRow++){
    		//从哪列开始，A表示第一列
    		for($currentColumn="A";$currentColumn<=$allColumn;$currentColumn++){
    			//数据坐标
    			$address=$currentColumn.$currentRow;
    			//读取到的数据，保存到数组$arr中
    			$data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
    		}
    		break;
    	}
		//弄一个combo的格式
		//[["name","姓名"],["sex","性别"],["email","邮箱"]];
		$excelCombo=array(//这里增加你需要的字段
				"name"=>"姓名",
				"sex"=>"性别",
				"email"=>"邮箱",
				"password"=>'密码'
		);
		$comboData=array();
		foreach ($excelCombo as $k=>$v){
			$tmp=array(
				$k,$v
			);
			$comboData[]=$tmp;
		}
		
    	$arr=array();
    	$excelFieldName=array(
    		"name"=>"姓名",	//(字段=》excel列标题，key与value互换位置)
    		"sex"=>"性别",	
    		"email"=>"邮箱",
    		"password"=>"密码"
    	);
    	//翻转（excel列标题=》字段）
    	$fileNameMysql=array_flip($excelFieldName);//现在数组是value=》key
    	//将数据转变为store形式，再传到前台文件导入grid
    	//[{"number":"A","fieldName":"\u59d3\u540d","sysField":"name"},{"number":"B","fieldName":"\u6027\u522b","sysField":"sex"}]
    	$titleData=$data[1];
    	foreach ($titleData as $k=>$v){
    			$arr[]=array('number'=>$k,'fieldName'=>$v,"sysField"=>$fileNameMysql[$v]);//得到excel的列、excel的列名、表字段
    	};
    	$this->ajaxRespond(array("success"=>true,"data"=>array(
    		"userData"=>$arr	
    	),'comboExcel'=>array("excelCombo"=>$comboData),'filename'=>$filename));  #响应上传文件的地址
    	//$this->ajaxRespond(array('userData'=>$arr));
        //$this->save_import($data,$arr);    	
    }
 
    //保存导入数据(根据“列-》字段关系”插入)
   protected function _excel_addExcel(){    	
    	//读取文件地址,依次分析插入数据库
    	$url=i('post.url');
    	import("Org.Util.PHPExcel");
    	import("Org.Util.PHPExcel.Reader.Excel5");
    	$PHPReader=new \PHPExcel_Reader_Excel5();
    	$PHPExcel=new \PHPExcel();
    	$PHPExcel=$PHPReader->load($url);
    	$currentSheet=$PHPExcel->getSheet(0);
    	$allColumn=$currentSheet->getHighestColumn();//总列数
    	$allRow=$currentSheet->getHighestRow();//总行数
    	//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    	for($currentRow=2;$currentRow<=$allRow;$currentRow++){//从第二行开始读取数据
	    	//从哪列开始，A表示第一列
	    	for($currentColumn="A";$currentColumn<=$allColumn;$currentColumn++){
	    	//数据坐标
	    	$address=$currentColumn.$currentRow;
	    		//读取到的数据，保存到数组$arr中
	    		$data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
	    	}
    	}
    	//print_r($data);exit;
    	$excelData=trim($_POST['excelData']);
    	$excel=json_decode($excelData,true);//将store的数据接收到，并为二维数组(加true就转换为数组形式)
    	//print_r($excel);exit;
    	$lineField=array();//列与字段的关系
    	foreach ($excel as $k=>$v){
    		$lineField[$v["number"]]=$v["sysField"];
    	}
    	$user=M('user');
    	$pattern='/\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*/';//邮箱的合法性
    	foreach ($data as $k=>$v){
    		//print_r($v);
    		$insertData=array();
    		if(empty($v)){
    			continue;
    		}
    	    foreach($v as $n=>$m){
    	    	$column=$n;
    	    	$columnValue=$m;
    	    	$field=$lineField[$column];//字段
    	    	if(empty($field)){
    	    		continue;
    	    	}
    	    	$insertData[$field]=trim($columnValue);
    	     }
    	     $insertData['register_time']=time();
    	     //print_r($insertData);exit;
    	     if($insertData){
    	     	if($insertData['email'] || $insertData['sex'] || $insertData['name'] ){
    	     		if(!preg_match($pattern,$insertData['email'])  || ($insertData['sex']!='男' && $insertData['sex']!='女') || empty($insertData['name'])){
    	     	        continue;
    	     		}
    	     	}
    	     }
    	     $result = M('user')->add($insertData);
    	}
    	//删除文件
    	if(file_exists($url)){
    		unlink($url);
    	}
    	$this->ajaxRespond(array('success'=>true));
    }
    
    ////保存导入数据(根据“excel标题-》字段关系”插入)
    protected  function _excel_loadExcel(){
    	$url=i('post.url');
    	import("Org.Util.PHPExcel");
    	import("Org.Util.PHPExcel.Reader.Excel5");
    	$PHPReader=new \PHPExcel_Reader_Excel5();
    	$PHPExcel=new \PHPExcel();
    	$PHPExcel=$PHPReader->load($url);
    	$currentSheet=$PHPExcel->getSheet(0);
    	$allColumn=$currentSheet->getHighestColumn();
    	$allRow=$currentSheet->getHighestRow();
    	for($i='A',$count=1;$i<$allColumn;$i++){
    		$count++;
    	}
    	for($currentRow=1;$currentRow<=$allRow;$currentRow++){
    		for($currentColumn="A";$currentColumn<=$allColumn;$currentColumn++){
    			$address=$currentColumn.$currentRow;
    			$header[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
    		}
    		break;
    	}
    	$title=$header[1];//Array([A] => 姓名   [B] => 性别 [C] => 邮箱 [D] => 时间)
    	for($currentRow=2;$currentRow<=$allRow;$currentRow++){
    		for($currentColumn="A";$currentColumn<=$allColumn;$currentColumn++){
    			$address=$currentColumn.$currentRow;
    			$data[$currentRow][$title[$currentColumn]]=$currentSheet->getCell($address)->getValue();
    		}
    	}
    	$pattern='/\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*/';//邮箱的合法性
    	$storeData=trim($_POST['excelData']);
    	$storeDataArray=json_decode($storeData,true);
    	$storeMysql=array();
    	foreach($storeDataArray as $k=>$v){
    		$storeMysql[$v['fieldName']]=$v['sysField'];
    	}
    	//print_r($storeMysql);exit;//Array([姓名] => name [性别] => sex [邮箱] => email [时间] => )
    	$user=M('user');
    	foreach($data as $k=>$v){
    		$arr=array();
    		if(empty($v)){
    			continue;
    		}
    		foreach ($v as $n=>$m){
    			$column=$n;
    			$columnValue=$m;
    			$field=$storeMysql[$column];
    			if(empty($field)){
    				continue;
    			}
    			$arr[$field]=trim($columnValue);
    		}
    		if($arr){
    			if($arr['name'] || $arr['email'] ||$arr['sex'] || $arr['password']){
    				$arr['password']=md5($arr['password']);
    				if(empty($arr['name'])||empty($arr['password']) || !preg_match($pattern, $arr['email']) || ($arr['sex']!='男' && $arr['sex']!='女')) {
    					continue;
    				}   				
    			}
    		}
    		$arr['register_time']=time();
    		$result=$user->add($arr);
    	}
    	if(file_exists($url)){
    		unlink($url);
    	}
    	$this->ajaxRespond(array("success"=>true));
    	
    }
    
    //此方法是excel表格导入与字段之间匹配的思想
    public  function demo(){
    	$a=array(
    		array("num"=>'A','title'=>'姓名','field'=>'name'),	
    		array("num"=>'B','title'=>'性别','field'=>'email'),
    		array("num"=>'C','title'=>'邮箱','field'=>'sex')
    	);
    	$b=array(
    		"A"=>"name",	
    		"B"=>"email",	
    		"C"=>"sex"
    	);
    	//
    	$line='A';
    	$value="小明";
    	$field=$b[$line];
    	//add数据		
    	$data=array(
    		 $field=$value  //array("name"=>'xx',"age"=>23)
    	);		
    }
    
    //导出数据方法
   protected function  _excel_goods_export()
    {
    	$goods_list = M('user')->select();
    	//print_r($goods_list);exit;
    	$data = array();
    	foreach ($goods_list as $k=>$goods_info){
    		$data[$k][id] = $goods_info['id'];
    		$data[$k][name] = $goods_info['name'];
    		$data[$k][sex] = $goods_info['sex'];
    		$data[$k][email] = $goods_info['email'];
    	}
    	//print_r($goods_list);
    	//print_r($data);exit;
    
    	foreach ($data as $field=>$v){
    		if($field == 'id'){
    			$headArr[]='序号';
    		}
    
    		if($field == 'name'){
    			$headArr[]='姓名';
    		}
    			
    		if($field == 'sex'){
    			$headArr[]='年龄';
    		}
    		
    		if($field == 'email'){
    			$headArr[]='邮箱';
    		}
    	}
    
    	$filename="goods_list";
    
    	$this->_excel_getExcel($filename,$headArr,$data);
    }
    
    
    protected  function _excel_getExcel($fileName,$headArr,$data){
    	//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
    	import("Org.Util.PHPExcel");
    	import("Org.Util.PHPExcel.Writer.Excel5");
    	import("Org.Util.PHPExcel.IOFactory.php");
    
    	$date = date("Y_m_d",time());
    	$fileName .= "_{$date}.xls";
    
    	//创建PHPExcel对象，注意，不能少了\
    	$objPHPExcel = new \PHPExcel();
    	$objProps = $objPHPExcel->getProperties();
    
    	//设置表头
    	$key = ord("A");
    	//print_r($headArr);exit;
    	foreach($headArr as $v){
    		$colum = chr($key);
    		$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
    		$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
    		$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
    		$key += 1;
    	}
    
    	$column = 2;
    	$objActSheet = $objPHPExcel->getActiveSheet();
    
    	//print_r($data);exit;
    	foreach($data as $key => $rows){ //行写入
    		$span = ord("A");
    		foreach($rows as $keyName=>$value){// 列写入
    			$j = chr($span);
    			$objActSheet->setCellValue($j.$column, $value);
    			$span++;
    		}
    		$column++;
    	}
    
    	$fileName = iconv("utf-8", "gb2312", $fileName);
    	//重命名表
    	//$objPHPExcel->getActiveSheet()->setTitle('test');
    	//设置活动单指数到第一个表,所以Excel打开这是第一个表
    	$objPHPExcel->setActiveSheetIndex(0);
    	header('Content-Type: application/vnd.ms-excel');
    	header("Content-Disposition: attachment;filename=\"$fileName\"");
    	header('Cache-Control: max-age=0');
    
    	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    	$objWriter->save('php://output'); //文件通过浏览器下载
    	exit;
    }
    
}